# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	columns = [
		{
			"label": _("BillNo"),
			"fieldtype": "Link",
			"fieldname": "name",
			"options": "SRM Sample RequestCaigou",
			"width": 180,
		},
		{
			"label": _("Status"),
			"fieldtype": "Data",
			"fieldname": "docstatus",
			"width": 60,
		},
		{
			"label": _("Material Name"),
			"fieldtype": "Data",
			"fieldname": "material_name",
			"width": 150,
		},
		{
			"label": _("Material Model"),
			"fieldtype": "Data",
			"fieldname": "material_model",
			"width": 150,
		},
		{
			"label": _("Material Number"),
			"fieldtype": "Data",
			"fieldname": "material_number",
			"width": 150,
		},
		{
			"label": _("Technical Require"),
			"fieldtype": "Data",
			"fieldname": "technical_require",
			"width": 150,
		},
		{
			"label": _("Require Date"),
			"fieldtype": "Date",
			"fieldname": "require_date",
			"width": 120,
		},
		{
			"label": _("Current Supplier"),
			"fieldtype": "Data",
			"fieldname": "current_supplier",
			"width": 180,
		},
		{
			"label": _("Description"),
			"fieldtype": "Data",
			"fieldname": "description",
			"width": 100,
		},
		{
			"label": _("申请人"),
			"fieldtype": "Data",
			"fieldname": "applicant_name",
			"width": 70,
		},
		{
			"label": _("Attachment"),
			"fieldtype": "Data",
			"fieldname": "attachment",
			"width": 80,
		},
	]

	return columns


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	docstatus, creation_from, creation_to = filters.get('docstatus'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if docstatus:
		conditions += f" AND s1.docstatus = '{docstatus}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""select s1.name, s1.require_date,
			case s1.docstatus when 0 then '保存'
					when 1 then '提交'
					when 2 then '取消' end as docstatus,
			s1.applicant_name,
			s1.technical_require,
			s1.creation,
			s1.material_name,
			s1.material_model,
			s1.material_number,
			s1.current_supplier,
			s1.description,
			concat('<a href=', IFNULL(s1.attachment, ''), '>', IFNULL(substring(s1.attachment, 10, 4), ''), '</a>') as attachment
			from `tabSRM Sample RequestCaigou` s1
			WHERE s1.creation >= '{creation_from}' and s1.creation <= '{creation_to}' {conditions}
			ORDER BY s1.creation DESC;
		""", as_dict=True
	)

	return dt
